package com.presalept.manipulatedata;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.presalept.polodo.ClientDO;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class RetrieveClientData {
	private String dbOneClientJson;
	private String dbAllClientJson;

	private List<ClientDO> ClientList;
	
	//get all Clients;
	
	public String GetClientlist()throws SQLException{
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from clientinfo";
		
		try{
		
			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbRs = dbPreStmt.executeQuery();	
			
			ClientList = CreateClientlist(dbRs);
			
			dbAllClientJson = CreateClientlistJson(ClientList);			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbAllClientJson;
		
	}
	
	//get one Client of inputting Clientid
	
	public String GetOneClient(int ClientId) throws SQLException{
		Connection dbConn = null;
		PreparedStatement dbPreStmt = null;
		ResultSet dbRs = null;
		String dbSql = "select * from event where clientid = ?";
		
		try{

			dbConn = DbManager.getConnection();
			
			dbPreStmt = dbConn.prepareStatement(dbSql);
			
			dbPreStmt.setInt(1, ClientId);
			
			dbRs = dbPreStmt.executeQuery();	
			
			dbOneClientJson = CreateOneClientJson(CreateOneClient(dbRs));			
			
		}finally{
			if (dbRs != null) dbRs.close();
			if (dbConn != null) dbConn.close();
			
		}
		return dbOneClientJson;	
		
	}
	
	
	//get all Clients
	private List<ClientDO> CreateClientlist(ResultSet dbRs) throws SQLException{
		
		List<ClientDO> clientlist = new ArrayList<ClientDO>();
		
		while(dbRs.next()){
			ClientDO clientdo = new ClientDO();
			clientdo.setClientId(dbRs.getInt("clientid"));
			clientdo.setClientName(dbRs.getString("clientname"));
			clientlist.add(clientdo);	
		}		
		return clientlist;
	}
	
	//get all Clients json
	
	private String CreateClientlistJson(List<ClientDO> clientlist){
		
		JSONArray jsonObject = JSONArray.fromObject(clientlist);
		return jsonObject.toString();	
	}
	
	//get one Client of inputting Clientid
	
	private ClientDO CreateOneClient(ResultSet dbRs) throws SQLException{
		
		dbRs.next();
		ClientDO clientdo = new ClientDO();
		clientdo.setClientId(dbRs.getInt("clientid"));
		clientdo.setClientName(dbRs.getString("clientname"));
		return clientdo;
	}
	
	//get one event json
	
	private String CreateOneClientJson(ClientDO clientdo){
		JSONObject jsonObject = JSONObject.fromObject(clientdo);
		return jsonObject.toString();
	}
}
